import pandas as pd
import numpy as np

def demonstrate_pandas_merging():
    """
    演示Pandas中数据融合的完整语法和实际应用
    """
    print("=" * 60)
    print("Pandas数据融合完整指南")
    print("=" * 60)
    
    # 创建示例数据
    print("1. 示例数据准备")
    print("-" * 30)
    
    # 员工基本信息表
    employees = pd.DataFrame({
        'employee_id': [101, 102, 103, 104, 105],
        'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
        'department': ['HR', 'Tech', 'Tech', 'Finance', 'HR'],
        'salary': [50000, 70000, 80000, 60000, 55000]
    })
    
    # 部门信息表
    departments = pd.DataFrame({
        'dept_id': [1, 2, 3],
        'dept_name': ['HR', 'Tech', 'Finance'],
        'manager': ['Carol', 'Frank', 'Grace']
    })
    
    # 项目分配表
    projects = pd.DataFrame({
        'project_id': [201, 202, 203, 204],
        'employee_id': [101, 102, 102, 105],
        'project_name': ['System Upgrade', 'Data Migration', 'Security Audit', 'UI Redesign'],
        'hours': [120, 80, 60, 100]
    })
    
    print("员工表:")
    print(employees)
    print("\n部门表:")
    print(departments)
    print("\n项目表:")
    print(projects)
    print()
    
    # 2. merge() 方法详解
    print("2. pd.merge() 方法详解")
    print("-" * 40)
    
    # 内连接 (默认)
    inner_merge = pd.merge(employees, projects, on='employee_id', how='inner')
    print("内连接 (inner) - 只保留匹配的记录:")
    print(inner_merge)
    print()
    
    # 左连接
    left_merge = pd.merge(employees, projects, on='employee_id', how='left')
    print("左连接 (left) - 保留左表所有记录:")
    print(left_merge)
    print()
    
    # 外连接
    outer_merge = pd.merge(employees, projects, on='employee_id', how='outer')
    print("外连接 (outer) - 保留所有记录:")
    print(outer_merge)
    print()
    
    # 多键连接
    dept_emp = pd.merge(employees, departments, left_on='department', 
                       right_on='dept_name', how='left')
    print("多键连接 - 员工与部门信息:")
    print(dept_emp)
    print()
    
    # 使用indicator参数跟踪数据来源
    merge_with_indicator = pd.merge(employees, projects, on='employee_id', 
                                   how='outer', indicator=True)
    print("使用indicator参数:")
    print(merge_with_indicator)
    print()
    
    # 3. concat() 方法详解
    print("3. pd.concat() 方法详解")
    print("-" * 40)
    
    # 创建额外的员工数据
    new_employees = pd.DataFrame({
        'employee_id': [106, 107],
        'name': ['Frank', 'Grace'],
        'department': ['Tech', 'Finance'],
        'salary': [75000, 65000]
    })
    
    # 纵向拼接 (行方向)
    vertical_concat = pd.concat([employees, new_employees], axis=0, ignore_index=True)
    print("纵向拼接 (axis=0):")
    print(vertical_concat)
    print()
    
    # 横向拼接 (列方向)
    employee_stats = pd.DataFrame({
        'employee_id': [101, 102, 103, 104, 105],
        'years_experience': [3, 5, 7, 2, 4],
        'performance_score': [85, 92, 78, 88, 90]
    })
    
    horizontal_concat = pd.concat([employees, employee_stats.drop('employee_id', axis=1)], axis=1)
    print("横向拼接 (axis=1):")
    print(horizontal_concat)
    print()
    
    # 4. join() 方法详解
    print("4. join() 方法详解")
    print("-" * 40)
    
    # 设置索引
    employees_indexed = employees.set_index('employee_id')
    projects_indexed = projects.set_index('employee_id')
    
    # 基于索引的连接
    index_join = employees_indexed.join(projects_indexed, how='left', lsuffix='_emp', rsuffix='_proj')
    print("基于索引的连接:")
    print(index_join)
    print()
    
    # 5. 高级应用场景
    print("5. 高级应用场景")
    print("-" * 40)
    
    # 场景1: 计算部门平均工资和项目总工时
    dept_summary = employees.groupby('department')['salary'].mean().reset_index()
    project_summary = projects.groupby('employee_id')['hours'].sum().reset_index()
    
    # 合并汇总数据
    summary_merge = pd.merge(employees[['employee_id', 'name', 'department']], 
                           project_summary, on='employee_id', how='left')
    summary_merge = pd.merge(summary_merge, dept_summary, on='department', how='left')
    summary_merge.columns = ['employee_id', 'name', 'department', 'total_hours', 'dept_avg_salary']
    
    print("员工项目工时与部门平均工资汇总:")
    print(summary_merge)
    print()
    
    # 场景2: 处理重复列名
    projects_alt = pd.DataFrame({
        'employee_id': [101, 102, 103],
        'name': ['Alice Smith', 'Bob Johnson', 'Charlie Brown'],  # 与员工表name不同
        'project_status': ['Completed', 'In Progress', 'Planning']
    })
    
    # 使用suffixes处理重复列名
    suffix_merge = pd.merge(employees, projects_alt, on='employee_id', 
                          suffixes=('_emp', '_proj'))
    print("使用suffixes处理重复列名:")
    print(suffix_merge)
    print()
    
    return employees, projects, departments

def performance_tips():
    """
    提供性能优化建议和最佳实践
    """
    print("6. 性能优化建议")
    print("-" * 40)
    print("✓ 大数据集使用inner连接减少内存占用")
    print("✓ 合并前确保键的数据类型一致")
    print("✓ 使用copy=False避免不必要的数据复制")
    print("✓ 考虑先用query()过滤数据再合并")
    print("✓ 对常用键建立索引提高连接速度")

# 运行演示
if __name__ == "__main__":
    emp, proj, dept = demonstrate_pandas_merging()
    performance_tips()
    
    print("\n" + "="*60)
    print("核心方法总结")
    print("="*60)
    print("1. merge()    - 类似SQL JOIN，基于列值连接")
    print("2. concat()   - 沿轴简单拼接，适合结构相同的数据") 
    print("3. join()     - 基于索引连接，语法更简洁")
    print("\n连接类型: inner(默认), left, right, outer")
    print("关键参数: on, how, suffixes, indicator")